import json
import sys
from pathlib import Path

# 获取项目根目录
root_dir = str(Path(__file__).resolve().parent.parent)
sys.path.append(root_dir)

from app.sql.mysql_main import main_opera

def insert_goods(goods) -> int:
    """
    插入新商品记录到数据库
    Returns:
        插入操作影响的行数
    Raises:
        DatabaseError: 数据库操作失败时抛出
    """
    try:
        # 2. 删除指定键
        goods.pop("spec_list", None)  # 删除 spec_list 键
        json_str = json.dumps(goods)  # 将字典转为字符串
        parsed_data = json.loads(json_str)  # 再将字符串解析为字典
        # 执行数据库操作
        # 1. 检查 goods_id 是否存在
        check_query = "SELECT 1 FROM goods WHERE goods_id = %(goods_id)s"
        exists = main_opera(check_query, {"goods_id": parsed_data["goods_id"]}, True)
        if exists:
            # 2. 存在则执行 UPDATE
            update_query = """
                           UPDATE goods
                           SET goods_no       = %(goods_no)s,
                               goods_name     = %(goods_name)s,
                               short_name     = %(short_name)s,
                               alias          = %(alias)s,
                               goods_type     = %(goods_type)s,
                               spec_count     = %(spec_count)s,
                               pinyin         = %(pinyin)s,
                               brand_no       = %(brand_no)s,
                               brand_name     = %(brand_name)s,
                               remark         = %(remark)s,
                               prop1          = %(prop1)s,
                               prop2          = %(prop2)s,
                               prop3          = %(prop3)s,
                               prop4          = %(prop4)s,
                               prop5          = %(prop5)s,
                               prop6          = %(prop6)s,
                               origin         = %(origin)s,
                               class_name     = %(class_name)s,
                               class_id       = %(class_id)s,
                               brand_id       = %(brand_id)s,
                               unit_name      = %(unit_name)s,
                               aux_unit_name  = %(aux_unit_name)s,
                               flag_name      = %(flag_name)s,
                               goods_modified = %(goods_modified)s,
                               deleted        = %(deleted)s,
                               unit           = %(unit)s,
                               aux_unit       = %(aux_unit)s,
                               flag_id        = %(flag_id)s,
                               properties     = %(properties)s,
                               version_id     = %(version_id)s,
                               modified       = NOW() -- 更新修改时间
                           WHERE goods_id = %(goods_id)s 
                           """
            result = main_opera(update_query, parsed_data, False)
            return result
        else:
            # 3. 不存在则执行 INSERT
            insert_query = """
                           INSERT INTO goods (goods_id, goods_no, goods_name, short_name, alias, goods_type, 
                                              spec_count, pinyin, brand_no, brand_name, remark, 
                                              prop1, prop2, prop3, prop4, prop5, prop6, 
                                              origin, class_name, class_id, brand_id, unit_name, 
                                              aux_unit_name, flag_name, goods_created, goods_modified, 
                                              deleted, unit, aux_unit, flag_id, properties, 
                                              version_id, modified, created) 
                           VALUES (%(goods_id)s, %(goods_no)s, %(goods_name)s, %(short_name)s, %(alias)s, 
                                   %(goods_type)s, 
                                   %(spec_count)s, %(pinyin)s, %(brand_no)s, %(brand_name)s, %(remark)s, 
                                   %(prop1)s, %(prop2)s, %(prop3)s, %(prop4)s, %(prop5)s, %(prop6)s, 
                                   %(origin)s, %(class_name)s, %(class_id)s, %(brand_id)s, %(unit_name)s, 
                                   %(aux_unit_name)s, %(flag_name)s, %(goods_created)s, %(goods_modified)s, 
                                   %(deleted)s, %(unit)s, %(aux_unit)s, %(flag_id)s, %(properties)s, 
                                   %(version_id)s, NOW(), %(created)s -- modified 自动设置为当前时间 
                                  ) 
                           """
            result = main_opera(insert_query, parsed_data, False)
            return result
    except Exception as e:
        # 这里应该记录日志而不是简单打印
        print(f"Error inserting goods: {e}")
        raise


# 插入商品规格
def insert_goods_spec(goods_spec)->int:
    try:
        # barcode_list
        # 2. 删除指定键
        goods_spec.pop("barcode_list", None)  # 删除 spec_list 键
        json_str = json.dumps(goods_spec)  # 将字典转为字符串
        parsed_data = json.loads(json_str)  # 再将字符串解析为字典
        # 1. 检查 goods_id 是否存在
        check_query = "SELECT 1 FROM goods_spec WHERE spec_id = %(spec_id)s"
        exists = main_opera(check_query, {"spec_id": parsed_data["spec_id"]}, True)
        if exists:
            update_query = """
                    UPDATE goods_spec
                    SET spec_code           = %(spec_code)s,
                        barcode             = %(barcode)s,
                        spec_name           = %(spec_name)s,
                        lowest_price        = %(lowest_price)s,
                        retail_price        = %(retail_price)s,
                        wholesale_price     = %(wholesale_price)s,
                        member_price        = %(member_price)s,
                        market_price        = %(market_price)s,
                        custom_price1       = %(custom_price1)s,
                        custom_price2       = %(custom_price2)s,
                        prop1               = %(prop1)s,
                        prop2               = %(prop2)s,
                        prop3               = %(prop3)s,
                        prop4               = %(prop4)s,
                        prop5               = %(prop5)s,
                        prop6               = %(prop6)s,
                        remark              = %(remark)s,
                        sale_score          = %(sale_score)s,
                        pack_score          = %(pack_score)s,
                        pick_score          = %(pick_score)s,
                        validity_days       = %(validity_days)s,
                        sales_days          = %(sales_days)s,
                        receive_days        = %(receive_days)s,
                        weight              = %(weight)s,
                        length              = %(length)s,
                        width               = %(width)s,
                        height              = %(height)s,
                        is_sn_enable        = %(is_sn_enable)s,
                        is_allow_neg_stock  = %(is_allow_neg_stock)s,
                        is_not_need_examine = %(is_not_need_examine)s,
                        is_zero_cost        = %(is_zero_cost)s,
                        is_lower_cost       = %(is_lower_cost)s,
                        is_not_use_air      = %(is_not_use_air)s,
                        tax_rate            = %(tax_rate)s,
                        large_type          = %(large_type)s,
                        spec_created        = %(spec_created)s,
                        spec_modified       = %(spec_modified)s,
                        img_url             = %(img_url)s,
                        spec_aux_unit_name  = %(spec_aux_unit_name)s,
                        spec_unit_name      = %(spec_unit_name)s,
                        deleted             = %(deleted)s,
                        tax_code_id         = %(tax_code_id)s,
                        is_single_batch     = %(is_single_batch)s,
                        washing_label       = %(washing_label)s,
                        unit                = %(unit)s,
                        aux_unit            = %(aux_unit)s,
                        flag_id             = %(flag_id)s,
                        img_key             = %(img_key)s,
                        barcode_count       = %(barcode_count)s,
                        plat_spec_count     = %(plat_spec_count)s,
                        postfix_val         = %(postfix_val)s,
                        last_date           = %(last_date)s,
                        replenish_type      = %(replenish_type)s,
                        is_popular          = %(is_popular)s,
                        replace_no          = %(replace_no)s,
                        spec_mask           = %(spec_mask)s,
                        replace_proportion  = %(replace_proportion)s,
                        extra_3             = %(extra_3)s,
                        modified            = %(modified)s, 
                        tax_code            = %(tax_code)s
                    WHERE spec_id = %(spec_id)s 
                    """
            result = main_opera(update_query, parsed_data, False)
            return result
        else:
            insert_query = """
                           INSERT INTO goods_spec (spec_id, goods_id, spec_no, spec_code, barcode, spec_name, 
                                                   lowest_price, retail_price, wholesale_price, member_price, market_price, 
                                                   custom_price1, custom_price2, prop1, prop2, prop3, prop4, prop5, prop6, 
                                                   remark, sale_score, pack_score, pick_score, validity_days, sales_days, 
                                                   receive_days, weight, length, width, height, is_sn_enable, 
                                                   is_allow_neg_stock, 
                                                   is_not_need_examine, is_zero_cost, is_lower_cost, is_not_use_air, 
                                                   tax_rate, 
                                                   large_type, spec_created, spec_modified, img_url, spec_aux_unit_name, 
                                                   spec_unit_name, deleted, tax_code_id, is_single_batch, washing_label, 
                                                   unit, aux_unit, flag_id, img_key, barcode_count, plat_spec_count, 
                                                   postfix_val, last_date, replenish_type, is_popular, replace_no, 
                                                   spec_mask, 
                                                   replace_proportion, extra_3, modified, created, tax_code) 
                           VALUES (%(spec_id)s, %(goods_id)s, %(spec_no)s, %(spec_code)s, %(barcode)s, %(spec_name)s, 
                                   %(lowest_price)s, %(retail_price)s, %(wholesale_price)s, %(member_price)s, 
                                   %(market_price)s, 
                                   %(custom_price1)s, %(custom_price2)s, %(prop1)s, %(prop2)s, %(prop3)s, %(prop4)s, 
                                   %(prop5)s, 
                                   %(prop6)s, 
                                   %(remark)s, %(sale_score)s, %(pack_score)s, %(pick_score)s, %(validity_days)s, 
                                   %(sales_days)s, 
                                   %(receive_days)s, %(weight)s, %(length)s, %(width)s, %(height)s, %(is_sn_enable)s, 
                                   %(is_allow_neg_stock)s, 
                                   %(is_not_need_examine)s, %(is_zero_cost)s, %(is_lower_cost)s, %(is_not_use_air)s, 
                                   %(tax_rate)s, 
                                   %(large_type)s, %(spec_created)s, %(spec_modified)s, %(img_url)s, %(spec_aux_unit_name)s, 
                                   %(spec_unit_name)s, %(deleted)s, %(tax_code_id)s, %(is_single_batch)s, %(washing_label)s, 
                                   %(unit)s, %(aux_unit)s, %(flag_id)s, %(img_key)s, %(barcode_count)s, %(plat_spec_count)s, 
                                   %(postfix_val)s, %(last_date)s, %(replenish_type)s, %(is_popular)s, %(replace_no)s, 
                                   %(spec_mask)s, 
                                   %(replace_proportion)s, %(extra_3)s, %(modified)s, %(created)s, %(tax_code)s) 
                           """
            result = main_opera(insert_query, parsed_data, False)
            return result
    except Exception as e:
        # 这里应该记录日志而不是简单打印
        print(f"Error inserting goods: {e}")
        raise

# 插入商品条形码
def insert_goods_brcode(goods_brcode) -> int:
    try:
        # 删除无关键（如有需要）
        goods_brcode.pop("barcode_list", None)
        json_str = json.dumps(goods_brcode)
        parsed_data = json.loads(json_str)  # 解析为字典

        # 提取必要的参数（确保 spec_id 和 goods_id 存在）
        spec_id = parsed_data["spec_id"]
        goods_id = parsed_data["goods_id"]  # 新增：获取 goods_id

        # 检查 barcode 表中是否存在记录（spec_id 和 goods_id 必须同时存在）
        check_query = """
            SELECT 1 FROM barcode 
            WHERE spec_id = %(spec_id)s AND goods_id = %(goods_id)s
        """
        exists = main_opera(
            check_query,
            {"spec_id": spec_id, "goods_id": goods_id},  # 传递两个参数
            True
        )

        if exists:
            # 更新逻辑
            update_query = """
                UPDATE barcode
                SET 
                    goods_id = %(goods_id)s,
                    barcode = %(barcode)s,
                    type = %(type)s,
                    is_master = %(is_master)s,
                    out_target_num = %(out_target_num)s,
                    target_num = %(target_num)s,
                    modified = %(modified)s
                WHERE spec_id = %(spec_id)s AND goods_id = %(goods_id)s
            """
            result = main_opera(update_query, parsed_data, False)
            return result
        else:
            # 插入逻辑
            insert_query = """
                INSERT INTO barcode (
                    spec_id, goods_id, barcode, type, is_master, 
                    out_target_num, target_num, modified
                ) VALUES (
                    %(spec_id)s, %(goods_id)s, %(barcode)s, %(type)s, %(is_master)s, 
                    %(out_target_num)s, %(target_num)s, %(modified)s
                )
            """
            result = main_opera(insert_query, parsed_data, False)
            return result

    except Exception as e:
        print(f"Error inserting barcode: {e}")
        raise


# 查询商品列表
def select_goods_list(page_no:int,page_size:int, goodsName:str)->list:
    # 2. 计算分页参数
    start_no = (page_no - 1) * page_size
    end_no = page_no * page_size
    try:
        # 1. 查询商品主信息
        query_goods = """
                      SELECT goods_id,goods_no,goods_name,short_name,alias,goods_type,spec_count,pinyin, 
                             brand_no,brand_name,remark,prop1,prop2,prop3,prop4,prop5,prop6, 
                             origin,class_name,class_id,brand_id,unit_name,aux_unit_name, 
                             flag_name,goods_created,goods_modified,deleted,unit,aux_unit, 
                             flag_id,properties,version_id,modified,created
                      FROM goods
                      """
        if  goodsName is not None:
            query_goods =  query_goods+" where goods_name like %(goods_name)s "
        query_goods = query_goods + """
             LIMIT %(end_no)s OFFSET %(page_no)s
             """
        parsed_data = {"page_no":start_no, "end_no":end_no, "goods_name":f"%{goodsName}%"}
        # 获取商品信息列表
        print(f"============================================")
        print(query_goods)
        print(f"============================================")
        result_goods = main_opera(query_goods, parsed_data, True)
        return result_goods
    except Exception as e:
        print(f"Error selecting goods: {e}")

# 查询商品规格列表
def select_goods_spec_list(goods_id) -> list:
    try:
        # 2. 查询规格信息
        query_specs = """
                      SELECT spec_id,goods_id,spec_no,spec_code,barcode,spec_name,lowest_price,retail_price,wholesale_price, 
                             member_price,market_price,custom_price1,custom_price2,prop1,prop2,prop3,prop4, 
                             prop5,prop6,remark,sale_score,pack_score,pick_score,validity_days,sales_days,receive_days,weight, 
                             length,width,height,is_sn_enable,is_allow_neg_stock,is_not_need_examine,is_zero_cost, 
                             is_lower_cost,is_not_use_air,tax_rate,large_type,spec_created,spec_modified,img_url,spec_aux_unit_name, 
                             spec_unit_name,deleted,tax_code_id,is_single_batch,washing_label,unit,aux_unit,flag_id,img_key, 
                             barcode_count,plat_spec_count,postfix_val,last_date,replenish_type,is_popular,replace_no,spec_mask, 
                             replace_proportion,extra_3, 
                             modified,created,tax_code
                      FROM goods_spec
                      WHERE goods_id = %(goods_id)s  
                      """
        params = {"goods_id":goods_id}
        result_spec = main_opera(query_specs, params, True)
        return result_spec
    except Exception as e:
        print(f"Error selecting goods: {e}")

# 查询商品条形码列表
def select_goods_barcode_list(goods_id,spec_id) -> list:
    try:
        # 3. 查询条形码信息（按规格分组）
        query_barcodes = """
                         SELECT spec_id,goods_id,barcode,type,is_master,out_target_num,target_num,modified
                         FROM barcode
                         WHERE spec_id = %(spec_id)s   and goods_id = %(goods_id)s; 
                         """
        params = {"spec_id":spec_id, "goods_id":goods_id}
        result_barcode = main_opera(query_barcodes, params, True)
        return result_barcode
    except Exception as e:
        print(f"Error selecting goods: {e}")

# 查询商品总数
def select_goods_count()->int:
    try:
        total_query = "SELECT COUNT(goods_id) as total_count FROM goods;"
        count = main_opera(total_query, None, True)
        return count[0].get("total_count", 0)
    except Exception as e:
        print(f"Error selecting goods count: {e}")
        return 0

# 查询商品品牌数量
def select_goods_brand_count()->int:
    try:
        total_query = "SELECT COUNT(brand_id) as total_count FROM goods_brand;"
        count = main_opera(total_query, None, True)
        return count[0].get("total_count", 0)
    except Exception as e:
        print(f"Error selecting goods brand count: {e}")
        return 0

# 查询商品品牌信息
def select_goods_brand_info()->dict:
    try:
        brand_query = """SELECT * FROM goods_brand;"""
        brand_info = main_opera(brand_query, None, True)
        return brand_info
    except Exception as e:
        print(f"Error selecting goods brand info: {e}")
        return []